﻿using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;
namespace socialnexus.classes.models
{
    /// <summary>
    /// Entry points are URLs that allow access to the site.
    /// This allows for pages containing only groups of users to be rendered
    /// rather than pages with many users.
    /// </summary>
    public class EntryPoint
    {
        public static string TableName = "EntryPoints";

        private int _EntryPointId;
        public int EntryPointId
        {
            get { return _EntryPointId; }
            set { _EntryPointId = value; }
        }

        private string _Name;
        public string Name
        {
            get { return _Name; }
            set { _Name = value; }
        }

        private string _Description;
        public string Description
        {
            get { return _Description; }
            set { _Description = value; }
        }

        private string _GUID;
        public string GUID
        {
            get { return _GUID; }
            set { _GUID = value; }
        }

        private bool _IsActive;
        public bool IsActive
        {
            get { return _IsActive; }
            set { _IsActive = value; }
        }

        public EntryPoint()
        {
            IsActive = true;
        }


        public static DataTable GetAll()
        {
            DataTable users = new DataTable(TableName);

            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["socialnexusConnectionString"].ConnectionString);
            con.Open();

            SqlCommand cmd = new SqlCommand("SELECT t.*, ISNULL(x.Total, 0) as NrOfUsers FROM " +
                TableName + " t LEFT JOIN (SELECT EntryPointId, COUNT(EntryPointId) AS Total " +
                " FROM EntryPoints_SiteUsers GROUP BY EntryPointId) x " +
                " ON t.EntryPointId = x.EntryPointId", con);
            SqlDataReader reader = cmd.ExecuteReader();
            users.Load(reader);

            con.Close();

            return users;
        }

        public static EntryPoint GetByGUID(string GUID)
        {
            EntryPoint e = null;

            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["socialnexusConnectionString"].ConnectionString);
            con.Open();

            SqlCommand cmd = new SqlCommand("SELECT * FROM EntryPoints WHERE GUID = @ID AND IsActive = 1", con);
            cmd.Parameters.Add(new SqlParameter("@ID", GUID));

            SqlDataReader reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                e = new EntryPoint();
                e.EntryPointId = int.Parse(reader["EntryPointId"].ToString());
                e.GUID = reader["GUID"].ToString();
                e.Name = reader["Name"].ToString();
                e.Description = reader["Description"].ToString();
                e.IsActive = reader["IsActive"].ToString() == "True";
            }
            con.Close();

            return e;
        }

        public static EntryPoint GetById(string ID)
        {
            EntryPoint e = null;

            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["socialnexusConnectionString"].ConnectionString);
            con.Open();

            SqlCommand cmd = new SqlCommand("SELECT * FROM EntryPoints WHERE EntryPointId = @ID AND IsActive = 1", con);
            cmd.Parameters.Add(new SqlParameter("@ID", ID));

            SqlDataReader reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                e = new EntryPoint();
                e.EntryPointId = int.Parse(reader["EntryPointId"].ToString());
                e.GUID = reader["GUID"].ToString();
                e.Name = reader["Name"].ToString();
                e.Description = reader["Description"].ToString();
                e.IsActive = reader["IsActive"].ToString() == "True";
            }
            con.Close();

            return e;
        }

        public void Save()
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["socialnexusConnectionString"].ConnectionString);
            con.Open();
            SqlCommand cmd = new SqlCommand("INSERT INTO " + TableName +
                " (Name, Description, GUID, IsActive) VALUES " +
                "(@NAME, @DESCRIPTION, @GUID, @ISACTIVE)", con);
            cmd.Parameters.Add(new SqlParameter("@NAME", Name ));
            cmd.Parameters.Add(new SqlParameter("@DESCRIPTION", Description));
            cmd.Parameters.Add(new SqlParameter("@GUID", GUID));
            cmd.Parameters.Add(new SqlParameter("@ISACTIVE", IsActive));

            cmd.ExecuteNonQuery();

            con.Close();
        }

        public void Update()
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["socialnexusConnectionString"].ConnectionString);
            con.Open();
            SqlCommand cmd = new SqlCommand("UPDATE " + TableName +
                " SET Name = @NAME, Description = @DESCRIPTION, GUID = @GUID, IsActive = @ISACTIVE " +
                " WHERE EntryPointId = @ID", con);
            cmd.Parameters.Add(new SqlParameter("@NAME", Name));
            cmd.Parameters.Add(new SqlParameter("@DESCRIPTION", Description));
            cmd.Parameters.Add(new SqlParameter("@GUID", GUID));
            cmd.Parameters.Add(new SqlParameter("@ISACTIVE", IsActive));
            cmd.Parameters.Add(new SqlParameter("@ID", EntryPointId));

            cmd.ExecuteNonQuery();

            con.Close();
        }

        public void addUser(string SiteUserId)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["socialnexusConnectionString"].ConnectionString);
            con.Open();
            SqlCommand cmd = new SqlCommand("INSERT INTO EntryPoints_SiteUsers " +
                " (EntryPointId, SiteUserId) VALUES " +
                "(@ENTRYPOINTID, @SITEUSERID)", con);
            cmd.Parameters.Add(new SqlParameter("@ENTRYPOINTID", EntryPointId));
            cmd.Parameters.Add(new SqlParameter("@SITEUSERID", SiteUserId));
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch { }
            con.Close();
        }

        public void removeUser(string SiteUserId)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["socialnexusConnectionString"].ConnectionString);
            con.Open();
            SqlCommand cmd = new SqlCommand("DELETE FROM EntryPoints_SiteUsers " +
                " WHERE EntryPointId = @ENTRYPOINTID AND SiteUserId = @SITEUSERID", con);
            cmd.Parameters.Add(new SqlParameter("@ENTRYPOINTID", EntryPointId));
            cmd.Parameters.Add(new SqlParameter("@SITEUSERID", SiteUserId));
            
            cmd.ExecuteNonQuery();
            
            con.Close();
        }

        public DataTable getUsers()
        {
            DataTable users = new DataTable(TableName);

            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["socialnexusConnectionString"].ConnectionString);
            con.Open();

            SqlCommand cmd = new SqlCommand("SELECT su.*, t.* FROM " + TableName + 
                " t INNER JOIN EntryPoints_SiteUsers epsu ON t.EntryPointId = epsu.EntryPointId " +
                " INNER JOIN SiteUsers su ON su.SiteUserId = epsu.SiteUserId" + 
                " WHERE t.EntryPointId = @ID AND su.IsActive = 1", con);
            cmd.Parameters.Add(new SqlParameter("@ID", EntryPointId));
            SqlDataReader reader = cmd.ExecuteReader();
            users.Load(reader);

            con.Close();

            return users;
        }

        public List<string> getUserIds()
        {
            List<string> userIds = new List<string>();
            DataTable users = getUsers();

            foreach (DataRow dr in users.Rows)
            {
                userIds.Add(dr["SiteUserId"].ToString());
            }
            return userIds;
        }

    }
}